using System;
using System.Data.SqlClient;
using System.Collections;
using System.Data;
using Common;
using DIV.DAL;

namespace DAL
{
	/// <summary>
	/// Summary description for DALBase.
	/// </summary>
	public class DALBase : IDisposable
	{
		#region Variables
		private string _connectionString;	
		protected SqlConnection _connection;
		private SqlTransaction _transaction;
		protected static DataTable _connectionPool; 
		#endregion

		#region Properties
		protected virtual Type AssemblyType
		{
			get
			{
				return typeof(DALBase);
			}
		}

		private SqlConnection Connection
		{
			get
			{
			    var drConnectionPool = _connectionPool.Rows.Find(System.Threading.Thread.CurrentThread);
			    return drConnectionPool != null
			               ? drConnectionPool["Connection"] as SqlConnection
			               : _connection;
			}
		}

		private SqlTransaction Transaction
		{
			get
			{
			    DataRow drConnectionPool = _connectionPool.Rows.Find(System.Threading.Thread.CurrentThread);
			    return drConnectionPool != null
			               ? drConnectionPool["Transaction"] as SqlTransaction
			               : _transaction;
			}
		}

		#endregion

		#region Construcion / Destruction code
		public DALBase()
		{
//			_resourceManager = new ResourceManager(this.AssemblyType.Namespace + ".DALMessage", 
//				Assembly.GetAssembly(this.AssemblyType));

		    _connectionString = string.Format("Data Source={0};Initial Catalog={1};User Id={2};Password={3};",
		                                      Controller.ReadString("Database"),
		                                      Controller.ReadString("DBName"),
		                                      Controller.ReadString("DBAccount"),
		                                      Controller.Decrypt(Controller.ReadString("DBPassword")));

			_connection = new SqlConnection(this._connectionString);
		
			if (_connectionPool == null)
			{
				_connectionPool = new DataTable();
				_connectionPool.Columns.Add("Thread", typeof(System.Threading.Thread));
				_connectionPool.Columns.Add("Connection", typeof(SqlConnection));
				_connectionPool.Columns.Add("Transaction", typeof(SqlTransaction));
				_connectionPool.PrimaryKey = new[]{_connectionPool.Columns["Thread"]};
			}
		}
		#endregion

		#region Private methods

		private int UpdateDataByDataTable(string selectCommandText, DataTable dataTable)
		{
			SqlCommand selectCommand = null;
			try
			{
				//Attention: Must use this.Transaction to get transaction from the pool.
				//DO NOT use _transaction directly!!!
				if (this.Transaction != null)
				{
					selectCommand = new SqlCommand(selectCommandText, this.Transaction.Connection, this.Transaction);
				}
				else
				{
					selectCommand = new SqlCommand(selectCommandText, _connection);
				}
				var da = new SqlDataAdapter(selectCommand);
			    var builder = new SqlCommandBuilder(da) {QuotePrefix = "[", QuoteSuffix = "]"};

			    builder.RefreshSchema();		
				da.MissingSchemaAction = MissingSchemaAction.AddWithKey;

				int numberOfRow = da.Update(dataTable);
				return numberOfRow;
			}
			catch (Exception ex)
			{
				throw ex;
			}
			finally
			{
				if (selectCommand != null)
				{
					selectCommand.Dispose();
				}

				if (this.Transaction == null)
				{
					_connection.Close();
				}
			}
		}

		private int UpdateDataByDataRows(string selectCommandText, DataRow[] dataRows)
		{
			SqlCommand selectCommand = null;
			try
			{
				if (this.Transaction != null)
				{
					selectCommand = new SqlCommand(selectCommandText, this.Transaction.Connection, this.Transaction);
				}
				else
				{
					selectCommand = new SqlCommand(selectCommandText, _connection);
				}
				var da = new SqlDataAdapter(selectCommand);
				var builder = new SqlCommandBuilder(da) {QuotePrefix = "[", QuoteSuffix = "]"};

			    builder.RefreshSchema();		
				da.MissingSchemaAction = MissingSchemaAction.AddWithKey;

				int numberOfRow = da.Update(dataRows);
				return numberOfRow;
			}
			catch (Exception ex)
			{
				throw ex;
			}
			finally
			{
				if (selectCommand != null)
				{
					selectCommand.Dispose();
				}

				if (this.Transaction == null)
				{
					_connection.Close();
				}
			}
		}
		#endregion

		#region Protected methods
		protected SqlParameter CreateSqlParamater(string fieldName, object fieldValue)
		{
			var sqlPara = new SqlParameter(string.Concat("@", fieldName), fieldValue);
			return sqlPara;
		}

		/// <summary>
		/// Builds insert command text for insert command using SqlParamter
		/// </summary>
		/// <param name="tableName">The name of table</param>
		/// <param name="dataToInsert">Set of fields and values to be inserted</param>
		/// <param name="param">Set of parameter names and values</param>
		/// <returns>Insert command text</returns>
		protected string BuildInsertCommandTextUsingParam(string tableName,
			Hashtable dataToInsert, 
			out SqlParameter[] param)
		{
			string insertString = string.Format("INSERT INTO [{0}](", tableName);
			string listOfField = string.Empty;
			string listOfParam = string.Empty;
			param = new SqlParameter[dataToInsert.Count];

			IDictionaryEnumerator iDic = dataToInsert.GetEnumerator();
			int count = -1;

			#region while(iDic.MoveNext())
			while (iDic.MoveNext())
			{
				count ++;
				if (iDic.Key.ToString() == string.Empty)
				{
					continue;
				}

				string paramName = string.Format("@{0}", iDic.Key.ToString());
				listOfField += string.Concat("[",iDic.Key.ToString(), "],");
				listOfParam += string.Format("{0},", paramName);
				param[count] = new SqlParameter(paramName, iDic.Value);
			}
			#endregion

			listOfField = listOfField.TrimEnd(',');

			listOfParam = listOfParam.TrimEnd(',');

			insertString += string.Concat(listOfField, ")", "VALUES(", listOfParam, ")");

			return insertString;
		}

		/// <summary>
		/// Builds insert command text for insert command
		/// </summary>
		/// <param name="tableName">The name of table</param>
		/// <param name="dataToInsert">Set of field names and values</param>
		/// <returns>Insert command text</returns>
		protected string BuildInsertCommandText(string tableName, Hashtable dataToInsert)
		{
			string masterInserSql = string.Format("INSERT INTO [{0}](", tableName);
			string listOfField = string.Empty;
			string listOfValue = string.Empty;

			IDictionaryEnumerator iDic = dataToInsert.GetEnumerator();

			#region while(iDic.MoveNext())
			while(iDic.MoveNext())
			{
				if(iDic.Key.ToString() == string.Empty)
				{
					continue;
				}

				listOfField += string.Concat(iDic.Key.ToString(), ",");
				listOfValue += string.Format("N'{0}',", iDic.Value.ToString());
			}
			#endregion

			if(listOfField.Length > 0)
			{
				listOfField = listOfField.Remove(listOfField.Length-1, 1);
			}

			if(listOfValue.Length > 0)
			{
				listOfValue = listOfValue.Remove(listOfValue.Length-1, 1);
			}

			masterInserSql += string.Concat(listOfField, ") VALUES(", listOfValue, ")");
			
			return masterInserSql;
		}

		/// <summary>
		/// Builds condtion string
		/// </summary>
		/// <param name="filterField">Set of field names and values</param>
		/// <returns>Condition string</returns>
		protected string BuildConditionString(Hashtable filterField)
		{
			string filterString = string.Empty;

			if(filterField != null && filterField.Count > 0)
			{
				#region foreach(string fieldName in filterField.Keys)
				foreach(string fieldName in filterField.Keys)
				{
					if(fieldName == string.Empty)
					{
						continue;
					}

					filterString += string.Format(" AND ({0}=N'{1}')", fieldName, filterField[fieldName].ToString());//Do not use "filterField[fieldName] as string" because it does not work when data type is not string, such as int, float, etc.
				}
				#endregion

				if(filterString.StartsWith(" AND"))
				{
					filterString = filterString.Substring(4);
				}
			}
			return filterString;
		}

		/// <summary>
		/// Builds condition string
		/// </summary>
		/// <param name="filterFieldName">Set of field names</param>
		/// <param name="filterFieldValue">Set of field values</param>
		/// <returns>Condtion string</returns>
		protected string BuildConditionString(string[] filterFieldName, string[] filterFieldValue)
		{
			if (filterFieldName == null || filterFieldValue == null)
			{
				return null;
			}

			if(filterFieldName.Length != filterFieldValue.Length)
			{
				string messgae = "ParamNotMatch";
				throw new ApplicationException(messgae);
			}

			var filterNameValue = new Hashtable();

			for(int i=0; i<filterFieldName.Length; i++)
			{
				if(filterNameValue.ContainsKey(filterFieldName[i]))
				{
					filterNameValue[filterFieldName[i]] = filterFieldValue[i];
				}
				else
				{
					filterNameValue.Add(filterFieldName[i], filterFieldValue[i]);
				}
			}

			return BuildConditionString(filterNameValue);
		}

		/// <summary>
		/// Builds update command text for update command
		/// </summary>
		/// <param name="tableName">The name of table</param>
		/// <param name="fieldValueToUpdate">Set of field name and values to be updated</param>
		/// <returns>Update command text</returns>
		protected string BuildUpdateCommandText(string tableName, Hashtable fieldValueToUpdate)
		{
			string updateString = string.Format("UPDATE [{0}]", tableName);
			string updateValue = string.Empty;
		
			if(fieldValueToUpdate.Count > 0)
			{
				#region foreach(string fieldName in fieldValueToUpdate.Keys)
				foreach (string fieldName in fieldValueToUpdate.Keys)
				{
					if(fieldName == string.Empty)
					{
						continue;
					}
					//comment out on Sep 06, 2007 & modify 
					//updateValue += string.Format(",{0}=N'{1}'", fieldName, fieldValueToUpdate[fieldName] as string);					
					updateValue += string.Format(",{0}=N'{1}'", fieldName, fieldValueToUpdate[fieldName].ToString());					
				}
				#endregion

				if (updateValue.StartsWith(","))
				{
					updateValue = updateValue.TrimStart(',');
				}
			}
			if (updateValue.Length > 0)
			{
				updateString += string.Concat(" SET ", updateValue);
			}

			return updateString;
		}

		/// <summary>
		/// Builds update command text for update command using SqlParameter
		/// </summary>
		/// <param name="tableName">The name of table</param>
		/// <param name="dataToUpdate">Set of fields and values to be updated</param>
		/// <param name="param">Set of parameter names and values</param>
		/// <returns>Update command text</returns>
		protected string BuildUpdateCommandTextUsingParam(string tableName, 
			Hashtable dataToUpdate,
			out SqlParameter[] param)
		{
			param = new SqlParameter[dataToUpdate.Count];
			string updateString = string.Format("UPDATE [{0}]", tableName);
			string updateFields = string.Empty;
		
			if(dataToUpdate.Count > 0)
			{
				int count = -1;

				#region foreach(string fieldName in fieldValueToUpdate.Keys)
				foreach (string fieldName in dataToUpdate.Keys)
				{
					if(fieldName == string.Empty)
					{
						continue;
					}
					count++;
					
					string paramName = string.Format("@{0}", fieldName);
					updateFields += string.Format(",[{0}]={1}", fieldName, paramName);
					param[count] = new SqlParameter(paramName, dataToUpdate[fieldName]);
				}
				#endregion

				if (updateFields.StartsWith(","))
				{
					updateFields = updateFields.TrimStart(',');
				}
			}
			if (updateFields.Length > 0)
			{
				updateString += string.Concat(" SET ", updateFields);
			}

			return updateString;
		}

		/// <summary>
		/// Builds delete command text for deleting data using SqlParameter
		/// </summary>
		/// <param name="tableName">The name of table</param>
		/// <param name="dataToUpdate">Set of fields and values to be updated</param>
		/// <param name="param">Set of parameter names and values</param>
		/// <returns>Update command text</returns>
		protected string BuildDeleteCommandTextUsingParam(string tableName, 
			Hashtable filterFields,
			bool isAndConjunction,
			out SqlParameter[] param)
		{
			param = new SqlParameter[filterFields.Count];
			string deleteString = string.Format("DELETE FROM [{0}]", tableName);
			string whereCondition = string.Empty;
		
			if(filterFields.Count > 0)
			{
				int count = -1;

				#region foreach(string fieldName in filterFields.Keys)
				foreach (string fieldName in filterFields.Keys)
				{
					if(fieldName == string.Empty)
					{
						continue;
					}
					count++;
					
					string paramName = string.Format("@{0}", fieldName);
					string conjunction = isAndConjunction ? "AND" : "OR ";
					whereCondition += string.Format("{0} [{1}]={2} ", conjunction, fieldName, paramName);
					param[count] = new SqlParameter(paramName, filterFields[fieldName]);
				}
				#endregion

				whereCondition = whereCondition.Substring(4);				
			}
			if (whereCondition.Length > 0)
			{
				deleteString += string.Concat(" WHERE ", whereCondition);
			}

			return deleteString;
		}

		/// <summary>
		/// Builds insert command text for insert command
		/// </summary>
		/// <param name="tableName">The name of table</param>
		/// <param name="fieldNameList">Set of field names to be selected</param>
		/// <param name="selectDistinct">Indecating whether select distinguish</param>
		/// <returns>Select command text</returns>
		protected string BuildSelectCommandText(string tableName, ICollection fieldNameList, bool selectDistinct)
		{
			string selectedField = string.Empty;
			string selectText = string.Empty;

			if (fieldNameList.Count > 0)
			{
				foreach (string field in fieldNameList)
				{
					selectedField += string.Format(",[{0}]", field);
				}
			}

			if (selectedField.Length > 0)
			{
				selectedField = selectedField.TrimStart(',');

				if (selectDistinct)
				{
					selectText = string.Format("SELECT DISTINCT {0} FROM [{1}]", selectedField, tableName);
				}
				else
				{
					selectText = string.Format("SELECT {0} FROM [{1}]", selectedField, tableName);
				}
			}
			return selectText;
		}

		protected int ExecuteNonQuery(CommandType commandType, string sqlString)
		{
		    return this.Transaction != null
		               ? SqlHelper.ExecuteNonQuery(this.Transaction, commandType, sqlString)
		               : SqlHelper.ExecuteNonQuery(this.Connection, commandType, sqlString);
		}

	    protected int ExecuteNonQuery(CommandType commandType, string sqlString, SqlParameter[] param)
	    {
	        return this.Transaction != null
	                   ? SqlHelper.ExecuteNonQuery(this.Transaction, commandType, sqlString, param)
	                   : SqlHelper.ExecuteNonQuery(this.Connection, commandType, sqlString, param);
	    }

	    protected int ExecuteNonQuery(string spName, params object[] parameterValues)
	    {
	        return this.Transaction != null
	                   ? SqlHelper.ExecuteNonQuery(this.Transaction, spName, parameterValues)
	                   : SqlHelper.ExecuteNonQuery(this.Connection, spName, parameterValues);
	    }

	    protected DataSet ExecuteDataSet(CommandType commandType, string sqlString)
	    {
	        return this.Transaction != null
	                   ? SqlHelper.ExecuteDataset(this.Transaction, commandType, sqlString)
	                   : SqlHelper.ExecuteDataset(this.Connection, commandType, sqlString);
	    }

	    protected DataSet ExecuteDataSet(string spName, params object[] parameterValues)
	    {
	        return this.Transaction != null
	                   ? SqlHelper.ExecuteDataset(this.Transaction,
	                                              spName,
	                                              parameterValues)
	                   : SqlHelper.ExecuteDataset(this.Connection,
	                                              spName,
	                                              parameterValues);
	    }

	    protected SqlDataReader ExcuteDataReader(string spName, params object[] parameterValues)
	    {
	        return this.Transaction != null
	                   ? SqlHelper.ExecuteReader(this.Transaction,
	                                             spName,
	                                             parameterValues)
	                   : SqlHelper.ExecuteReader(this.Connection,
	                                             spName,
	                                             parameterValues);
	    }

	    protected object ExecuteScalar(CommandType commandType, string sqlString)
	    {
	        return this.Transaction != null
	                   ? SqlHelper.ExecuteScalar(this.Transaction, CommandType.Text, sqlString)
	                   : SqlHelper.ExecuteScalar(this.Connection, CommandType.Text, sqlString);
	    }

	    protected object ExecuteScalar(CommandType commandType, string sqlString, params SqlParameter[] sqlParams)
	    {
	        return this.Transaction != null
	                   ? SqlHelper.ExecuteScalar(this.Transaction, CommandType.Text, sqlString, sqlParams)
	                   : SqlHelper.ExecuteScalar(this.Connection, CommandType.Text, sqlString, sqlParams);
	    }

	    protected object ExecuteScalar(string spName, params object[] paramValues)
	    {
	        return this.Transaction != null
	                   ? SqlHelper.ExecuteScalar(this.Transaction, spName, paramValues)
	                   : SqlHelper.ExecuteScalar(this.Connection, spName, paramValues);
	    }

	    #endregion

		#region Public methods
		public void BeginTransaction()
		{
			if (_connection == null)
			{
				_connection = new SqlConnection(_connectionString);
			}
			
			if (_connection.State == ConnectionState.Closed)
			{
				_connection.Open();
			}

			if (_transaction == null)
			{
				_transaction = _connection.BeginTransaction();
			}

			//Insert shared connection to connection pool
			DataRow drConnectionPool = _connectionPool.Rows.Find(System.Threading.Thread.CurrentThread);

			if (drConnectionPool == null)
			{
				_connectionPool.Rows.Add(new object[]{System.Threading.Thread.CurrentThread, _connection, _transaction});
			}
		}

		public void CommitTransaction()
		{
			try
			{
				_transaction.Commit();
				_transaction = null;
			}
			catch (Exception ex)
			{
				throw new ApplicationException("CouldnotCommitTrans");
			}
			finally
			{
				if (_connection != null)
				{
					if (_connection.State != ConnectionState.Closed)
					{
						_connection.Close();
					}
				}

				//remove shared connection from connection pool
				DataRow drConnectionPool = _connectionPool.Rows.Find(System.Threading.Thread.CurrentThread);
				if (drConnectionPool != null)
				{
					_connectionPool.Rows.Remove(drConnectionPool);
				}
			}
		}

		public void RollbackTransaction()
		{
			try
			{
				if (_transaction != null)
				{
					_transaction.Rollback();
					_transaction.Dispose();
					_transaction = null;
				}
			}
			catch (Exception ex)
			{
				throw new ApplicationException("CouldnotRollbackTrans");
			}
			finally
			{
				if (_connection != null)
				{
					if (_connection.State != ConnectionState.Closed)
					{
						_connection.Close();
					}
				}

				//remove shared connection from connection pool
				DataRow drConnectionPool = _connectionPool.Rows.Find(System.Threading.Thread.CurrentThread);
				if (drConnectionPool != null)
				{
					_connectionPool.Rows.Remove(drConnectionPool);
				}
			}
		}

		public void DeleteData(string tableName, string[] filterFieldName, string[] filterFieldValue)
		{			
			string sqlDeleteString = string.Format("DELETE FROM [{0}] ", tableName);
			string filterString = string.Empty;
			
			#region for(int i = 0; i < filterFieldName.Length; i++)
			for(int i=0; i<filterFieldName.Length; i++)
			{
				filterString += string.Format(" AND {0}='{1}'", filterFieldName[i], filterFieldValue[i]);
			}
			#endregion

			if (filterString.Length > 0)
			{
				if(filterString.StartsWith(" AND"))
				{
					filterString = filterString.Substring(4);
				}
			}
			sqlDeleteString += string.Concat(" WHERE ", filterString);
			try
			{
				this.ExecuteNonQuery(CommandType.Text, sqlDeleteString);
			}
			catch(Exception ex)
			{
				throw new ApplicationException(ex.Message);
			}
		}

		/// <summary>
		/// Deletes data with input parameters is Hashtable
		/// Mai added on Aug 20, 2007 
		/// </summary>
		/// <param name="tableName"></param>
		/// <param name="filterFieldName"></param>
		/// <param name="filterFieldValue"></param>
		public void DeleteData(string tableName, Hashtable filterValues)
		{			
			SqlParameter[] param;
			string sqlDeleteString = BuildDeleteCommandTextUsingParam(tableName, filterValues, true, out param) ;
		
			try
			{
				this.ExecuteNonQuery(CommandType.Text, sqlDeleteString, param);
			}
			catch(Exception ex)
			{
				throw new ApplicationException("CannotExecuteCmd");
			}
		}

		public void DeleteAllData(string tableName)
		{
			string deleteString = string.Format("DELETE FROM [{0}]", tableName);
			try
			{
				this.ExecuteNonQuery(CommandType.Text, deleteString);
			}
			catch(Exception ex)
			{
				throw ex;
			}
		}


		/// <summary>
		/// Get Data from dataset.
		/// </summary>		
		/// <returns></returns>	
		/* Added by Do Tran on date 2008-09-01 */
		public DataSet GetData( string spName, object[] parameters )
		{	
			DataSet objDs = new DataSet();
			objDs = this.ExecuteDataSet( spName, parameters );

			return objDs;
		}
		/* End */
		/// <summary>
		/// Get Data from Table Name.
		/// </summary>
		/// <param name="tableName">table Name input</param>
		/// <param name="filterFieldName"></param>
		/// <param name="filterFieldValue"></param>
		/// <returns></returns>	
		public DataTable GetData(string tableName, string[] filterFieldName, string[] filterFieldValue) 
		{
			// get data from table Name
			string sqlSelectData = string.Format("SELECT * FROM [{0}] ", tableName);									
			string filterString = this.BuildConditionString(filterFieldName, filterFieldValue);

			if (!string.IsNullOrEmpty(filterString)) 
			{
				if(filterString.StartsWith(" AND")) 
				{
					filterString = filterString.Substring(4);
				}
				sqlSelectData += string.Format(" WHERE {0}", filterString);
			}

			try 
			{ 
				DataSet ds = this.ExecuteDataSet(CommandType.Text, sqlSelectData);
				return ds.Tables[0];
			}
			catch (Exception ex) 
			{
				throw new ApplicationException("CouldnotGetData");
			}
		}

		public DataTable GetData(string tableName,
			bool getDistinct,
			string[] filterFields,
			object[] filterValues,
			params string[] fieldsToGet)
		{
			Hashtable filterFieldValue = new Hashtable();

			for (int i=0; i< filterFields.Length; i++)
			{
				filterFieldValue.Add(filterFields[i], filterValues[i]);
			}

			return this.GetData(tableName, getDistinct, filterFieldValue, fieldsToGet);
		}

		public DataTable GetData(string tableName, Hashtable filterField)
		{
			string sql = string.Format("SELECT * FROM [{0}]", tableName);
			string conditionString = BuildConditionString(filterField);
			if (conditionString != string.Empty)
			{
				sql += string.Concat(" WHERE ", conditionString);
			}
			return this.GetData(sql);
		}

		public DataTable GetData(string sql)
		{
			try
			{
				DataSet ds = this.ExecuteDataSet(CommandType.Text, sql);
				return ds.Tables[0];
			}
			catch (Exception ex)
			{
				throw ex;
			}
		}
		public DataTable GetData(string tableName, string conditionString)
		{
			string sql = string.Format("SELECT * FROM [{0}]", tableName);
			if (conditionString != string.Empty)
			{
				sql += string.Format(" WHERE {0}", conditionString);
			}
			return GetData(sql);
		}

		public DataTable GetData(string tableName, bool getDistinct, params string[] fieldToGet)
		{
			string sql = this.BuildSelectCommandText(tableName, fieldToGet, getDistinct);
			DataTable dtData = this.GetData(sql);
			return dtData;
		}

		public DataTable GetData(string tableName,
			bool getDistinct,
			Hashtable filterFieldValue,
			params string[] fieldToGet)
		{
			string sql = this.BuildSelectCommandText(tableName, fieldToGet, getDistinct);
			string conditionString = this.BuildConditionString(filterFieldValue);
			if (conditionString.Length > 0)
			{
				sql += string.Concat(" WHERE", conditionString);
			}
			DataTable dtData = this.GetData(sql);
			return dtData;
		}

		public object GetScalar(string tableName, string fieldToGet, Hashtable filterFieldValue)
		{
			string selectString = string.Format("SELECT TOP 1 [{0}] FROM [{1}]", fieldToGet, tableName);
			string filterString = this.BuildConditionString(filterFieldValue);

			if (filterString.Length > 0)
			{
				selectString += string.Format(" WHERE {0}", filterString);
			}

			return this.ExecuteScalar(CommandType.Text, selectString);
		}
		public object GetScalar(string sql)
		{
			return this.ExecuteScalar(CommandType.Text, sql);
		}

		public object GetScalar(string tableName, string fieldToGet, string[] filterFieldName, object[] filteFieldValue)
		{
			Hashtable filterFieldValue = new Hashtable();

			for (int i=0; i< filterFieldName.Length; i++)
			{
				if (filterFieldName[i] == string.Empty)
				{
					continue;
				}

				filterFieldValue.Add(filterFieldName[i], filteFieldValue[i]);
			}

			return this.GetScalar(tableName, fieldToGet, filterFieldValue);
		}

		public void UpdateData(string tableName,
			string[] fieldToUpdate,
			object[] valueToUpdate,
			string[] filterFieldName,
			object[] filterFieldValue)
		{
			if (fieldToUpdate.Length != valueToUpdate.Length)
			{
				throw new ApplicationException("ParamNotMatch");
			}
			if (filterFieldName.Length != filterFieldValue.Length)
			{
				throw new ApplicationException("ParamNotMatch");
			}
			Hashtable fieldsToUpdate = new Hashtable();
			Hashtable filterFields = new Hashtable();

			#region for(int i=0; i<fieldToUpdate.Length; i++)
			for(int i=0; i<fieldToUpdate.Length; i++)
			{
				if(fieldToUpdate[i] == string.Empty) 
				{
					continue;
				}
				fieldsToUpdate.Add(fieldToUpdate[i], valueToUpdate[i]);
			}
			#endregion

			#region for(int i=0; i<filterFieldName.Length; i++)
			for(int i=0; i<filterFieldName.Length; i++)
			{
				if(filterFieldName[i] == string.Empty) 
				{
					continue;
				}
				filterFields.Add(filterFieldName[i], filterFieldValue[i]);
			}
			#endregion

			this.UpdateData(tableName, fieldsToUpdate, filterFields);
		}

		/// <summary>
		/// Update data by specified table name and a set of fields
		/// </summary>
		/// <param name="tableName">The name of table to be updated</param>
		/// <param name="fieldsToUpdate">Set of field names and values</param>
		/// <param name="filterFields">Set of filter field names and values</param>
		public void UpdateData(string tableName, Hashtable fieldsToUpdate, Hashtable filterFields)
		{
			if (fieldsToUpdate == null || fieldsToUpdate.Count == 0)
			{
				// throw exception
				return;
			}

			SqlParameter[] param;
			string updateString = BuildUpdateCommandTextUsingParam(tableName, fieldsToUpdate, out param);
			string filterString = this.BuildConditionString(filterFields);

			if (filterString.Length > 0)
			{
				updateString += string.Concat(" WHERE ", filterString);
			}

			try
			{
				this.ExecuteNonQuery(CommandType.Text, updateString, param);
			}
			catch (Exception ex)
			{
				throw new ApplicationException("CannotExecuteCmd");
			}
		}

		/// <summary>
		/// Update data table by DataTable
		/// </summary>
		/// <param name="dataTable">DataTable contain data to be updated</param>
		/// <returns>Number of rows are updated</returns>
		public int UpdateDataByDataTable(DataTable dataTable)
		{
			try
			{
				string selectCommandText = string.Format("SELECT * FROM [{0}]", dataTable.TableName);
				return this.UpdateDataByDataTable(selectCommandText, dataTable);
			}
			catch (Exception ex)
			{
				throw ex;
			}
		}

		public int UpdateDataByDataRows(DataRow[] dataRows)
		{
			try
			{
				if (dataRows.Length == 0)
				{
					return 0;
				}

				string selectCommandText = string.Format("SELECT * FROM [{0}]", dataRows[0].Table.TableName);
				return this.UpdateDataByDataRows(selectCommandText, dataRows);
			}
			catch (Exception ex)
			{
				throw ex;
			}
		}

		/// <summary>
		/// Update data table with specified fields by DataTable
		/// </summary>
		/// <param name="dataTable">DataTable contain data to be updated</param>
		/// <param name="fields">List of fields to be updated</param>
		/// <returns>Number of rows are updated</returns>
		public int UpdateDataByDataTable(DataTable dataTable, params string[] fields)
		{
			try
			{
				// Can not use DISTINCT inside SELECT command for building SqlCommandBuilder
				string selectCommandText = this.BuildSelectCommandText(dataTable.TableName, fields, false);
				return this.UpdateDataByDataTable(selectCommandText, dataTable);
			}
			catch (Exception ex)
			{
				throw ex;
			}
		}

		public int UpdateDataByDataRows(DataRow[] dataRows, params string[] fields)
		{
			try
			{
				if (dataRows.Length == 0)
				{
					return 0;
				}

				// Can not use DISTINCT inside SELECT command for building SqlCommandBuilder
				string selectCommandText = this.BuildSelectCommandText(dataRows[0].Table.TableName, fields, false);
				return this.UpdateDataByDataRows(selectCommandText, dataRows);
			}
			catch (Exception ex)
			{
				throw ex;
			}
		}
		/// <summary>
		/// Checks whether data is existing by sql
		/// </summary>
		/// <param name="sql"></param>
		/// <returns></returns>
		public bool IsExist(string sql)
		{
			return this.ExecuteScalar(CommandType.Text, sql) != null;
		}

		/// <summary>
		/// Checks whether data is existing by specified filter fields
		/// </summary>
		/// <param name="tableName"></param>
		/// <param name="filterField"></param>
		/// <param name="fieldValue"></param>
		/// <returns></returns>
		public bool IsExist(string tableName, string[] filterField, object[] fieldValue)
		{
			string filterString = string.Empty;

			var sqlParam = new SqlParameter[filterField.Length];
			for (int i = 0; i < filterField.Length; i++)
			{
				filterString += string.Concat(" AND ", filterField[i], "=@", filterField[i]);
				sqlParam[i] = this.CreateSqlParamater(filterField[i], fieldValue[i]);
			}

			if (filterString != string.Empty)
			{
				filterString = filterString.Substring(4);// remove 'AND'
			}

			string sqlSelect = string.Format("SELECT COUNT(*) FROM [{0}] WHERE {1}", tableName, filterString);
			object numberOfRow = this.ExecuteScalar(CommandType.Text, sqlSelect, sqlParam);

			if (numberOfRow != null)
			{
				if ((int)numberOfRow > 0)
				{
					return true;
				}
			}

			return false;
		}

		/// <summary>
		/// Inserts data into specified table
		/// </summary>
		/// <param name="tableName">The name of table to be inserted</param>
		/// <param name="fieldsToInsert">Set of fields and values to be inserted</param>
		public void InsertData(string tableName, Hashtable fieldsToInsert)
		{
			try
			{
				SqlParameter[] param;
				string inserSql = this.BuildInsertCommandTextUsingParam(tableName, 
					fieldsToInsert, 
					out param);

				this.ExecuteNonQuery(CommandType.Text, inserSql, param);
			}
			catch (Exception ex)
			{
				throw ex;
			}
		}

		/// <summary>
		/// Inserts a record to database.
		/// </summary>
		/// <param name="tableName">The table to insert.</param>
		/// <param name="?"></param>
		public void InsertData(string tableName, string[] fieldsToInsert, object[] valuesToInsert)
		{

			if (fieldsToInsert.Length != valuesToInsert.Length)
			{
				throw new ApplicationException("ParamNotMatch");
			}

			var fieldsValuesToInsert = new Hashtable();
			for (int i=0; i<fieldsToInsert.Length; i++)
			{
				fieldsValuesToInsert.Add(fieldsToInsert[i], valuesToInsert[i]);
			}
			this.InsertData(tableName, fieldsValuesToInsert);
		}

		public bool CheckExist(string tableName, string[] filterField, string[] fieldValue)
		{
			string filterString = string.Empty;

			for (int i = 0; i < filterField.Length; i++)
			{
				filterString += string.Format(" {0} = '{1}'", filterField[i], fieldValue[i]);
				filterString += "AND ";
			}

			if (filterString != string.Empty)
			{
				filterString = filterString.TrimEnd();// remove 'AND'
				filterString = filterString.Substring(0, filterString.Length -3);    
			}

			string sqlSelect = string.Format("SELECT 1 FROM {0} WHERE {1}", tableName, filterString);
			object numberOfRow = this.ExecuteScalar(CommandType.Text, sqlSelect);

			if (numberOfRow != null)
			{
				if ((int)numberOfRow > 0)
				{
					return true;
				}
			}

			return false;
		}

		#endregion

		#region IDisposable Members

		public void Dispose()
		{
			if(_connection != null)
			{
				if (_connection.State != ConnectionState.Closed)
				{
					_connection.Close();
				}
			}
		}

		#endregion
	}
}
